/* *********************************************************************** *
 * File   : NorthwindProvider.cs                          Part of Sitecore *
 * Version: 2.1.0                                         www.sitecore.net *
 *                                                                         *
 *                                                                         *
 * Purpose: Simple dataprovider implementation                             *
 *                                                                         *
 * Bugs   : None known.                                                    *
 *                                                                         *
 * Status : Published.                                                     *
 *                                                                         *
 * Copyright (C) 1999-2007 by Sitecore A/S. All rights reserved.           *
 *                                                                         *
 * This work is the property of:                                           *
 *                                                                         *
 *        Sitecore A/S                                                     *
 *        Meldahlsgade 5, 4.                                               *
 *        1613 Copenhagen V.                                               *
 *        Denmark                                                          *
 *                                                                         *
 * This is a Sitecore published work under Sitecore's                      *
 * shared source license.                                                  *
 *                                                                         *
 * *********************************************************************** */

using System;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Transactions;
using Sitecore.Collections;
using Sitecore.Configuration;
using Sitecore.Data.IDTables;
using Sitecore.Data.Items;
using Sitecore.Data.Sql;
using Sitecore.Caching;
using Sitecore.Reflection;
using System.Collections.Specialized;

namespace Sitecore.Data.DataProviders
{
   public class NorthwindDataProvider : DataProvider
   {
      #region Variables
      string connectionString;
      string table;
      string fieldsNames;
      string idField;
      string filter;
      string templateID;
      string parentItemID;
      string hostDatabase;
      string nameField;
      static string messageNotVersions = "Item don't support versioning";

      readonly string prefix;

      // fields
      string[] fieldNames;

      #endregion

      #region ctor

      public NorthwindDataProvider(
       string connection,  // Connection string
       string table,       // Table name
       string fieldsNames,  // Fields name divided by pipe separator
       string idField,     // ID Field name (primary key)
       string nameField,   // Name field name (name for content editor)
       string filter,      // "Where" clause
       string templateID,// Template name
       string parentItemID, //Parent item ID
       string hostDatabase // Database into which we integrate
       )
      {
         prefix = ToString();

         connectionString = connection;
         this.table = table;
         this.fieldsNames = fieldsNames;
         this.idField = idField;
         this.nameField = nameField;
         this.filter = filter;
         this.templateID = templateID;
         this.parentItemID = parentItemID;
         fieldNames = StringUtil.Split(string.Concat(this.idField, ",", this.fieldsNames), ',', true);
         this.hostDatabase = hostDatabase;
         ModifyBase();
         CacheOptions.DisableAll = true;
      }

      #endregion

      #region provider methods

      public override ItemDefinition GetItemDefinition(ID id, CallContext context)
      {
         ItemDefinition newItem = null;
         if (CanProcessChild(id, context))
         {
            object orifinalID = GetOriginalRecordID(id);

            StringBuilder sqlSelect = new StringBuilder();
            sqlSelect.AppendFormat("SELECT {0} FROM {1}", nameField, table);
            sqlSelect.Append(GetConditions(orifinalID));

            string[] result = SqlUtil.GetStringArray(sqlSelect.ToString(), connectionString);
            if (result.Length > 0)
            {
               string itemName = result[0];
               if (string.IsNullOrEmpty(itemName))
               {
                  itemName = "unnamed item";
               }

               newItem = new ItemDefinition(id, itemName, MainDB.Templates[new ID(templateID)].ID, ID.Null);
               ((ICacheable)newItem).Cacheable = false;
            }
         }
         return newItem;
      }

      public override FieldList GetItemFields(ItemDefinition item, VersionUri version, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            if (context.DataManager.DataSource.ItemExists(item.ID))
            {
               ReflectionUtil.CallMethod(
                  typeof(ItemCache), CacheManager.GetItemCache(context.DataManager.Database),
                  "RemoveItem", true, true, new object[] { item.ID });
            }

            CoreItem.Builder builder = CreateItemBuilder(item, context.DataManager);
            return builder.ItemData.Fields;
         }
         return null;
      }

      public override IDList GetChildIDs(ItemDefinition item, CallContext context)
      {
         if (CanProcessParent(item.ID, context))
         {

            ID parentID = item.ID;
            string[] ids = LoadDataID();
            IDList idList = new IDList();

            foreach (string itemId in ids)
            {
               IDTableEntry idEntry = IDTable.GetID(ToString(), itemId);
               ID newID;
               if (idEntry == null)
               {
                  newID = ID.NewID;
                  IDTable.Add(ToString(), itemId, newID, parentID);
               }
               else
               {
                  newID = idEntry.ID;
               }
               idList.Add(newID);
            }            
            context.DataManager.Database.Caches.DataCache.Clear();
            return idList;
          }
         return null;
      }

      public override ID GetParentID(ItemDefinition item, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            context.Abort();
            return GetParentID(item.ID);
         }
         return base.GetParentID(item, context);
      }

      public override bool CreateItem(ID itemID, string itemName, ID tempID, ItemDefinition parent, CallContext context)
      {
          if (new ID(templateID) == tempID && new ID(parentItemID) == parent.ID)
          {
              string fields = GetFieldsName();
              StringCollection valuesBuilder = new StringCollection();
              foreach (string field in fieldNames)
              {
                  if (field == nameField)
                  {
                      valuesBuilder.Add(String.Format("'{0}'", itemName));
                      continue;
                  };
                  if (field == idField)
                  {
                      valuesBuilder.Add(String.Format("'{0}'", ID.Encode(itemID).Substring(0, 4)));
                      continue;
                  };
                  valuesBuilder.Add("''");
              }
              string values = StringUtil.StringCollectionToString(valuesBuilder, " ,");
              Insert(fields, values);
              IDTable.Add(ToString(), ID.Encode(itemID).Substring(0, 4), itemID, parent.ID);              
              context.Abort();
              return true;
          }
          return false;
      }

      public override bool SaveItem(ItemDefinition item, ItemChanges changes, CallContext context)
      {
         bool isFilterValueChanged = false;
         if (CanProcessChild(item.ID, context))
         {
            StringBuilder sqlString = new StringBuilder();
            sqlString.AppendFormat("UPDATE {0} SET ", table);
            StringBuilder newValues = new StringBuilder(string.Empty);
            SqlCommand cmd = new SqlCommand();

            foreach (FieldChange field in changes.FieldChanges)
            {
               string fieldName = context.DataManager.DataEngine.GetItem(field.FieldID,
                                  Globalization.Language.Current, new Version(1)).Name;

               if (IsContainField(fieldName))
               {
                  newValues.AppendFormat("{0}=@{0},", fieldName);
                  cmd.Parameters.Add(new SqlParameter(fieldName, field.Value));
               }
               if (filter.Contains(field.Definition.Name))
               {
                  isFilterValueChanged = true;
               }
            }

            if (newValues.Length > 0)
            {
               sqlString.Append(newValues);
               sqlString = sqlString.Remove(sqlString.Length - 1, 1);
               sqlString.AppendFormat(" WHERE {0}=@{0}", idField);
               cmd.Parameters.Add(new SqlParameter(idField, GetOriginalRecordID(item.ID)));
               cmd.CommandText = sqlString.ToString();
               ExecuteNonQuery(cmd);
            }
            if (isFilterValueChanged)
            {
               RemoveIDTableEntry(item.ID);
            }
            context.Abort();
            return true;
         }
         return false;
      }

      public override bool CopyItem(ItemDefinition source, ItemDefinition destination, string copyName, ID copyID, CallContext context)
      {

          if (new ID(templateID) == source.TemplateID && new ID(parentItemID) == destination.ID)
          {
              string fields = GetFieldsName();
              StringCollection valuesBuilder = new StringCollection();
              Item sourceItem = Context.ContentDatabase.GetItem(source.ID);

              foreach (string field in fieldNames)
              {
                  if (field == nameField)
                  {
                      valuesBuilder.Add(String.Format("'{0}'", copyName));
                      continue;
                  };
                  if (field == idField)
                  {
                      valuesBuilder.Add(String.Format("'{0}'", ID.Encode(copyID).Substring(0, 4)));
                      continue;
                  };
                  valuesBuilder.Add(String.Format("'{0}'", (sourceItem[field] != null) ? sourceItem[field] : ""));
              }
              string values = StringUtil.StringCollectionToString(valuesBuilder, " ,");
              Insert(fields, values);
              IDTable.Add(ToString(), ID.Encode(copyID).Substring(0, 4), copyID, destination.ID);
              context.Abort();
              return true;
          }
          return false;
      }

      void ExecuteNonQuery(SqlCommand cmd)
      {
         using (SqlConnection connection = new SqlConnection(connectionString))
         {
            connection.Open();
            cmd.Connection = connection;
            cmd.ExecuteNonQuery();
         }
      }

      public override ID GetRootID(CallContext context)
      {
         return base.GetRootID(context);
      }

      public override bool DeleteItem(ItemDefinition item, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            Delete(GetDatabaseID(item.ID));
            IDTable.RemoveID(ToString(), item.ID);

            return base.DeleteItem(item, context);
         }
         return false;
      }

      public override LanguageCollection GetLanguages(CallContext context)
      {
         return null;
      }

      #region Version

      public override int AddVersion(ItemDefinition item, VersionUri version, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            context.Abort();
            Context.ClientPage.ClientResponse.Alert(messageNotVersions);
            return 1;
         }
         return base.AddVersion(item, version, context);
      }

      public override VersionUriList GetItemVersions(ItemDefinition item, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            VersionUriList result = new VersionUriList();
            result.Add(Globalization.Language.Current, new Version(1));
            context.Abort();
            return result;
         }
         return base.GetItemVersions(item, context);
      }

      public override bool RemoveVersion(ItemDefinition item, VersionUri version, CallContext context)
      {
         if (CanProcessChild(item.ID, context))
         {
            context.Abort();
            return false;
         }
         return base.RemoveVersion(item, version, context);
      }

      #endregion

      #endregion

      #region private methods

      public void Insert(string addfields, string addvalues)
      {
         using (TransactionScope ts = new TransactionScope())
         {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
               sqlConnection.Open();
               string sqlCommand = string.Format("INSERT INTO {0} ({1}) VALUES ({2})",
                                                table, addfields, addvalues);
               new SqlCommand(sqlCommand, sqlConnection).ExecuteNonQuery();
            }
            ts.Complete();
         }
      }

      public void Delete(string id)
      {
         using (TransactionScope ts = new TransactionScope())
         {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
               sqlConnection.Open();
               string sqlCommand = string.Format("DELETE FROM {0} WHERE {1}='{2}'", table, idField, id);
               new SqlCommand(sqlCommand, sqlConnection).ExecuteNonQuery();
            }
            ts.Complete();
         }
      }

      // set DEFAULT dependence in relation between Customer and Order table.  
      void ModifyBase()
      {
         using (TransactionScope ts = new TransactionScope())
         {
            using (SqlConnection sqlConnection = new SqlConnection(connectionString))
            {
               sqlConnection.Open();
               string sqlCommand = "ALTER TABLE Orders DROP CONSTRAINT FK_Orders_Customers";
               new SqlCommand(sqlCommand, sqlConnection).ExecuteNonQuery();
               sqlCommand = "ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ON DELETE SET DEFAULT";
               new SqlCommand(sqlCommand, sqlConnection).ExecuteNonQuery();
            }
            ts.Complete();
         }
      }

      string GetConditions(object databaseID)
      {
         string result = string.Format(" WHERE ({0}='{1}')", idField, databaseID);
         if (filter.Length > 0)
         {
            result += string.Format(" AND ({0})", filter);
         }
         return result;
      }

      CoreItem.Builder CreateItemBuilder(ItemDefinition item, DataManager dataManager)
      {
         CoreItem.Builder result = new CoreItem.Builder(item.ID, item.Name, item.TemplateID, dataManager);
         StringBuilder sqlSelect = new StringBuilder();
         sqlSelect.AppendFormat("SELECT {0} FROM {1}", fieldsNames, table);
         sqlSelect.Append(GetConditions(GetOriginalRecordID(item.ID)));
         using (SqlDataReader sqlResult = ExecuteReader(sqlSelect.ToString(), connectionString))
         {
            if (sqlResult.Read())
            {
               for (int i = 0; i < sqlResult.FieldCount; i++)
               {
                  result.AddField(sqlResult.GetName(i), ToSitecoreValue(sqlResult.GetValue(i)));
               }
            }
         }
         return result;
      }

      SqlDataReader ExecuteReader(string sqlSelect, string connectionString, params object[] parameters)
      {
         SqlConnection connection = new SqlConnection(connectionString);
         SqlCommand commandReader = new SqlCommand(sqlSelect, connection);
         connection.Open();
         return commandReader.ExecuteReader(CommandBehavior.CloseConnection);
      }

      string GetFieldsName()
      {
         StringBuilder str = new StringBuilder();
         foreach (string field in fieldNames)
         {
            str.Append(field).Append(", ");
         }
         return str.Remove(str.Length - 2, 2).ToString();
      }

      string ToSitecoreValue(object val)
      {
         if (val is DateTime)
         {
            return DateUtil.ToIsoDate((DateTime)val);
         }
         return val.ToString();
      }

      bool CanProcessParent(ID id, CallContext context)
      {
         Item item = MainDB.Items[id];
         bool canProduce = false;
         if (ID.Parse(parentItemID) == item.ID)
         {
            canProduce = true;
         }
         return canProduce;
      }

      string GetDatabaseID(ID id)
      {
         return IDTable.GetKeys(prefix, id)[0].Key;
      }

      bool CanProcessChild(ID id, CallContext context)
      {
         if (IDTable.GetKeys(prefix, id).Length > 0)
         {
            return true;
         }
         return false;
      }

      object GetOriginalRecordID(ID id)
      {
         IDTableEntry[] idEntries = IDTable.GetKeys(prefix, id);
         if (idEntries != null && idEntries.Length > 0)
         {
            return idEntries[0].Key;
         }
         return null;
      }

      ID GetParentID(ID id)
      {
         IDTableEntry[] idEntries = IDTable.GetKeys(prefix, id);
         if (idEntries != null && idEntries.Length > 0)
         {
            return idEntries[0].ParentID;
         }
         return null;
      }

      void RemoveIDTableEntry(ID id)
      {
         IDTable.RemoveID(prefix, id);
      }

      bool IsContainField(string fieldName)
      {
         foreach (string field in fieldNames)
         {
            if (field == fieldName)
            {
               return true;
            }
         }
         return false;
      }

      string[] LoadDataID()
      {
         StringBuilder sqlSelect = new StringBuilder();
         sqlSelect.AppendFormat("SELECT {0} FROM {1}", idField, table);
         if (filter.Trim().Length > 0)
         {
            sqlSelect.AppendFormat(" WHERE {0}", filter);
         }

         return SqlUtil.GetStringArray(sqlSelect.ToString(), connectionString); 
      }

      Database MainDB
      {
         get
         {
            return Factory.GetDatabase(hostDatabase);
         }
      }
      #endregion private methods
   }
}
